﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Net;


namespace CCPSIP
{
    public class RobotKit
    {
        private static string[] kAdminEmailList = { "ch_chou@ccp.com.tw", "hj_huang@ccp.com.tw",
                                                "mike_lin@ccp.com.tw", "chuck_lan@ccp.com.tw"
                                              };
        private const string kReportEmailAddr = "ccpsip_report@ccp.com.tw";

        private const string kRobotServiceSalt = "zRxr3SNM9L7bn53XfGxu";

        /// <summary>取得Oracle連線字串(ISENDER_ORACLE)</summary>
        private static string GetConnStrIsenderOracle()
        {
            return "SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.52.195)(PORT=1521)))(CONNECT_DATA =(SERVER=DEDICATED)(SID=UNIDB)));uid=ISENDER;pwd=aKhJJFA2sWfEsQz0Zm5iwQi9gjiVND";
        }

        /// <summary>取得機器人權限列表</summary>
        /// <param name="userInfo">SipUserInfo物件</param>
        /// <returns>機器人Array</returns>
        public static RobotInfo[] getRobots(SipUserInfo userInfo)
        {
            List<RobotInfo> robotList = new List<RobotInfo>();
            OracleConnection connOracle = new OracleConnection(GetConnStrIsenderOracle());
            try
            {
                connOracle.Open();

                //【01】取得User有權限的機器人
                //
                string selStrOra = "SELECT ROBOT_ID, ROBOT_CNAME, INPUT_TYPE, ICON_TIME, ICON_SIZE"
                  + " FROM ISENDER_ROBOT R"
                  + " WHERE R.ROBOT_ID IN ("
                  + "   SELECT S.ROBOT_ID FROM ISENDER_SERVICE S"
                  + "   WHERE S.SERVICE_ID IN ("
                  + "     SELECT T.SERVICE_ID FROM ISENDER_SERVICE_RIGHT T"
                  + "     WHERE   (T.USER_TYPE='PPS_CODE'  AND T.USER_ID=:PPS_CODE)"
                  + "          OR (T.USER_TYPE='PHONE'     AND T.USER_ID=:PHONE)"
                  + "          OR (T.USER_TYPE='DEPT_LIKE' AND :DEPT_NO LIKE T.USER_ID||'%')"
                  + "   )"
                  + "   AND S.SERVICE_ID NOT IN ("
                  + "     SELECT T.SERVICE_ID FROM ISENDER_SERVICE_RIGHT T"
                  + "     WHERE  T.USER_TYPE='BLOCK' AND T.USER_ID=:PPS_CODE"
                  + "   )"
                  + "   GROUP BY S.ROBOT_ID"
                  + " )"
                  + " OR R.ROBOT_ID IN ("
                  + "   SELECT S.ROBOT_ID FROM ISENDER_SERVICE S"
                  + "   WHERE S.ANYONE_USE='Y'" + (SipKit.isCCPUser(userInfo.UserType) ? " OR S.CCP_USE='Y'" : "")
                  + "   GROUP BY S.ROBOT_ID"
                  + " )"
                  + " ORDER BY R.ROBOT_SEQ";

                OracleCommand selCmdOra = new OracleCommand(selStrOra, connOracle);
                selCmdOra.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = userInfo.PPS_CODE;
                string FullPhone = string.Format("{0}{1}", userInfo.CountryCode, userInfo.MobileNum);
                selCmdOra.Parameters.Add("PHONE", OracleType.VarChar).Value = FullPhone;
                selCmdOra.Parameters.Add("DEPT_NO", OracleType.VarChar).Value = userInfo.DEPT_NO;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmdOra);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    robotList.Add(new RobotInfo(row));
                }
            }
            catch
            {
            }
            finally
            {
                connOracle.Close();
            }
            return robotList.ToArray();
        }


        /// <summary>
        /// 取得可使用的機器人Service列表
        /// </summary>
        /// <param name="userInfo">SipUserInfo物件</param>
        /// <param name="ROBOT_ID">機器人ID</param>
        /// <returns>機器人Service Array</returns>
        public static RobotServiceInfo[] getServices(SipUserInfo userInfo, string ROBOT_ID)
        {
            List<RobotServiceInfo> serviceList = new List<RobotServiceInfo>();
            OracleConnection connOracle = new OracleConnection(GetConnStrIsenderOracle());
            try
            {
                connOracle.Open();
                OracleCommand selCmdOra = null;

                string selStrOra = "SELECT R.ROBOT_ID, R.ROBOT_CNAME,"
                  + "   S.SERVICE_ID, S.SERVICE_NAME, S.SERVICE_URL, S.CONTACT, S.CONTACT_DESC"
                  + " FROM ISENDER_ROBOT R, ISENDER_SERVICE S"
                  + " WHERE S.ROBOT_ID=R.ROBOT_ID AND S.ROBOT_ID=:ROBOT_ID"
                  + " AND ("
                  + "   S.SERVICE_ID IN ("
                  + "     SELECT T.SERVICE_ID FROM ISENDER_SERVICE_RIGHT T"
                  + "     WHERE (   (T.USER_TYPE='PPS_CODE'  AND T.USER_ID=:PPS_CODE)"
                  + "            OR (T.USER_TYPE='PHONE'     AND T.USER_ID=:PHONE)"
                  + "            OR (T.USER_TYPE='DEPT_LIKE' AND :DEPT_NO LIKE T.USER_ID||'%') )"
                  + "     GROUP BY T.SERVICE_ID"
                  + "   )"
                  + "   OR S.ANYONE_USE='Y'" + (SipKit.isCCPUser(userInfo.UserType) ? " OR S.CCP_USE='Y'" : "")
                  + " )"
                  + " AND S.SERVICE_ID NOT IN ("
                  + "   SELECT T.SERVICE_ID FROM ISENDER_SERVICE_RIGHT T"
                  + "   WHERE  T.USER_TYPE='BLOCK' AND T.USER_ID=:PPS_CODE"
                  + " )"
                  + " ORDER BY S.SV_SEQ, S.SERVICE_ID";

                selCmdOra = new OracleCommand(selStrOra, connOracle);
                selCmdOra.Parameters.Add("ROBOT_ID", OracleType.VarChar).Value = ROBOT_ID;
                selCmdOra.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = userInfo.PPS_CODE;
                string FullPhone = string.Format("{0}{1}", userInfo.CountryCode, userInfo.MobileNum);
                selCmdOra.Parameters.Add("PHONE", OracleType.VarChar).Value = FullPhone;
                selCmdOra.Parameters.Add("DEPT_NO", OracleType.VarChar).Value = userInfo.DEPT_NO;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmdOra);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    serviceList.Add(new RobotServiceInfo(row));
                }
            }
            catch (Exception ex)
            {
                ChrisKit.SkipVar(ex);
            }
            finally
            {
                connOracle.Close();
            }
            return serviceList.ToArray();
        }


        /// <summary>
        /// 取得機器人服務資訊
        /// </summary>
        /// <param name="SERVICE_ID">服務ID</param>
        /// <param name="Password">服務密碼</param>
        /// <returns>若通過驗證則回傳RobotServiceInfo物件，否則null</returns>
        public static RobotServiceInfo getServiceInfo(string SERVICE_ID, string Password)
        {
            if (string.IsNullOrEmpty(SERVICE_ID) || string.IsNullOrEmpty(Password))
                return null;

            //【01】計算Password Hash
            //
            string hash1 = HashKit.getHashStr(Password, HashType.SHA1);
            string seed1 = string.Format("{0}_{1}", hash1, kRobotServiceSalt);
            string PwdHash = HashKit.getHashStr(seed1, HashType.SHA256);


            //【02】驗證帳號密碼，取得Service Info
            //
            OracleConnection connOracle = new OracleConnection(GetConnStrIsenderOracle());
            try
            {
                connOracle.Open();
                string selStrOra = "SELECT R.ROBOT_ID, R.ROBOT_CNAME,"
                  + "   S.SERVICE_ID, S.SERVICE_NAME, S.SERVICE_URL, S.CONTACT, S.CONTACT_DESC"
                  + " FROM ISENDER_ROBOT R, ISENDER_SERVICE S"
                  + " WHERE S.ROBOT_ID=R.ROBOT_ID AND S.SERVICE_ID=:SERVICE_ID"
                  + "   AND :PWD_HASH IN (S.PWD_HASH, S.PWD_HASH2)";
                OracleCommand selCmdOra = new OracleCommand(selStrOra, connOracle);
                selCmdOra.Parameters.Add("SERVICE_ID", OracleType.VarChar).Value = SERVICE_ID;
                selCmdOra.Parameters.Add("PWD_HASH", OracleType.VarChar).Value = PwdHash;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmdOra);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);


                //【99】回傳Service Info
                //
                if (dataSet.Tables[0].Rows.Count > 0)
                    return new RobotServiceInfo(dataSet.Tables[0].Rows[0]);
            }
            catch (Exception ex)
            {
                ChrisKit.SkipVar(ex);
            }
            finally
            {
                connOracle.Close();
            }
            return null;
        }



        private static string[] kAllowRobotIPs = { "203.69.132.", "192.168.52.",
                                               "192.168.19.1",//iSender-CloudDisk
                                               "192.168.209.4"//漳州廠煙霧偵測Server
                                             };

        /// <summary>
        /// 檢查是否是合法的機器人Service IP
        /// </summary>
        /// <param name="ipAddr">IP Address</param>
        /// <returns>合法的機器人Service IP回傳true，否則false</returns>
        public static bool isAllowRobotIP(string ipAddr)
        {
            foreach (string kAllowIP in kAllowRobotIPs)
            {
                if (ipAddr.StartsWith(kAllowIP))
                    return true;
            }
            return false;
        }


        /// <summary>
        /// 由 ChatID 判斷是否為機器人
        /// </summary>
        /// <param name="ChatID">聊天ID(對方帳號或ClientGID)</param>
        /// <returns>ChatID是機器人回傳true，否則false</returns>
        public static bool isRobot(string ChatID)
        {
            if (ChatID.StartsWith("Robot"))
                return true;
            else
                return false;
        }


        /// <summary>
        /// 取得機器人名稱
        /// </summary>
        /// <param name="ROBOT_ID">機器人ID</param>
        /// <returns>若有此機器人則回傳名稱，否則null</returns>
        public static string getRobotCName(string ROBOT_ID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrIsenderOracle());
            try
            {
                connOracle.Open();
                string selStrOra = "SELECT ROBOT_CNAME FROM ISENDER_ROBOT"
                  + " WHERE ROBOT_ID=:ROBOT_ID";
                OracleCommand selCmdOra = new OracleCommand(selStrOra, connOracle);
                selCmdOra.Parameters.Add("ROBOT_ID", OracleType.VarChar).Value = ROBOT_ID;
                string ROBOT_CNAME = Convert.ToString(selCmdOra.ExecuteScalar());
                if (string.IsNullOrEmpty(ROBOT_CNAME))
                    return ROBOT_ID;
                else
                    return ROBOT_CNAME;
            }
            catch (Exception ex)
            {
                ChrisKit.SkipVar(ex);
            }
            finally
            {
                connOracle.Close();
            }
            return null;
        }


        /// <summary>
        /// 呼叫機器人Service
        /// </summary>
        /// <param name="userInfo">SipUserInfo物件</param>
        /// <param name="service">RobotServiceInfo物件</param>
        /// <param name="Command">指令全文</param>
        public static void CallService(SipUserInfo userInfo, RobotServiceInfo service, string Command)
        {
            Dictionary<string, string> paramDict = new Dictionary<string, string>();

            try
            {
                System.Text.Encoding encoding = System.Text.Encoding.UTF8;
                HttpWebRequest webReq = (HttpWebRequest)WebRequest.Create(service.SERVICE_URL);
                ServicePointManager.ServerCertificateValidationCallback = delegate { return true; };
                webReq.ProtocolVersion = HttpVersion.Version11;
                webReq.Method = "POST";
                webReq.Timeout = 120 * 1000;
                webReq.KeepAlive = false;
                webReq.AllowAutoRedirect = false;
                webReq.Proxy = new WebProxy();
                webReq.ContentType = "application/x-www-form-urlencoded; charset=utf-8";

                paramDict["SIP_Num"] = userInfo.SIP_Num;
                paramDict["PPS_CODE"] = Convert.ToString(userInfo.PPS_CODE);
                paramDict["MobileNum"] = string.Format("{0}{1}", userInfo.CountryCode, userInfo.MobileNum);
                paramDict["CustomName"] = userInfo.CustomName;
                paramDict["UserDir"] = userInfo.SIP_Num;
                if (SipKit.isHQMobileExt(userInfo.SIP_Num))
                {
                    paramDict["UserDir"] = SipKit.getHQDesktopExt(userInfo.SIP_Num);
                }
                paramDict["UUID"] = userInfo.UUID;
                paramDict["Command"] = Command;

                string paramStr = ChrisKit.DictToQueryStr(paramDict, encoding);

                byte[] postBytes = encoding.GetBytes(paramStr);
                webReq.ContentLength = postBytes.Length;
                using (System.IO.Stream reqStream = webReq.GetRequestStream())
                {
                    reqStream.Write(postBytes, 0, postBytes.Length);
                    reqStream.Flush();
                    reqStream.Close();
                }
            }
            catch (Exception ex)
            {
                string errorMsg = ChrisKit.DictToJSON(paramDict, true);
                errorMsg += Environment.NewLine + Environment.NewLine;
                errorMsg += ChrisKit.Ex2JSON(ex, true);
                ChrisKit.SendCCPEmail(kReportEmailAddr, kAdminEmailList, null, "【通知】Error: 呼叫機器人Service", errorMsg);
            }
        }


    }


    /// <summary>
    /// 機器人資訊
    /// </summary>
    public class RobotInfo
    {
        private string _ROBOT_ID;   //機器人ID
        private string _ROBOT_CNAME;//機器人名稱
        private string _INPUT_TYPE; //Input類型
        private string _ICON_TIME;  //Icon更新時間
        private string _ICON_SIZE;  //Icon檔案大小

        public string ROBOT_ID { get { return this._ROBOT_ID; } }
        public string ROBOT_CNAME { get { return this._ROBOT_CNAME; } }
        public string INPUT_TYPE { get { return this._INPUT_TYPE; } }
        public string ICON_TIME { get { return this._ICON_TIME; } }
        public string ICON_SIZE { get { return this._ICON_SIZE; } }

        public string ICON_LAST_UPDATE { get { return this._ICON_TIME; } } //2014-06-27:待新版上線後移除


        public RobotInfo(DataRow row)
        {
            _ROBOT_ID = Convert.ToString(row["ROBOT_ID"]);
            _ROBOT_CNAME = Convert.ToString(row["ROBOT_CNAME"]);
            _INPUT_TYPE = Convert.ToString(row["INPUT_TYPE"]);
            if (row["ICON_SIZE"] != DBNull.Value)
            {
                _ICON_TIME = ChrisKit.toUTCTimeStr(row["ICON_TIME"]);
                _ICON_SIZE = Convert.ToString(row["ICON_SIZE"]);
            }
        }
    }


    /// <summary>
    /// 機器人服務資訊
    /// </summary>
    public class RobotServiceInfo
    {
        private string _ROBOT_ID;   //機器人ID
        private string _ROBOT_CNAME;//機器人名稱
        private string _SERVICE_ID;  //服務ID
        private string _SERVICE_NAME;//服務名稱
        private string _SERVICE_URL; //服務URL
        private string _CONTACT;     //服務負責人員工編號
        private string _CONTACT_DESC;//客服連絡資訊

        public string ROBOT_ID { get { return this._ROBOT_ID; } }
        public string ROBOT_CNAME { get { return this._ROBOT_CNAME; } }
        public string SERVICE_ID { get { return this._SERVICE_ID; } }
        public string SERVICE_NAME { get { return this._SERVICE_NAME; } }
        public string SERVICE_URL { get { return this._SERVICE_URL; } }
        public string CONTACT { get { return this._CONTACT; } }
        public string CONTACT_DESC { get { return this._CONTACT_DESC; } }


        public RobotServiceInfo(DataRow row)
        {
            _ROBOT_ID = Convert.ToString(row["ROBOT_ID"]);
            _ROBOT_CNAME = Convert.ToString(row["ROBOT_CNAME"]);
            _SERVICE_ID = Convert.ToString(row["SERVICE_ID"]);
            _SERVICE_NAME = Convert.ToString(row["SERVICE_NAME"]);
            _SERVICE_URL = Convert.ToString(row["SERVICE_URL"]);
            _CONTACT = Convert.ToString(row["CONTACT"]);
            _CONTACT_DESC = Convert.ToString(row["CONTACT_DESC"]);
        }
    }


}